# -*- coding: utf-8 -*-
"""数据库模型"""

import sqlite3
import json
from pathlib import Path
from datetime import datetime
from typing import Dict, List, Optional


class Database:
    """数据库管理类"""
    
    def __init__(self, db_path="data/ai_worker.db"):
        """初始化数据库"""
        self.db_path = Path(db_path)
        self.db_path.parent.mkdir(parents=True, exist_ok=True)
        self.init_database()
    
    def get_connection(self):
        """获取数据库连接"""
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        return conn
    
    def init_database(self):
        """初始化数据库表"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        # 项目表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS projects (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                description TEXT,
                ai_provider TEXT DEFAULT 'doubao',
                ai_model TEXT,
                ai_key_id TEXT,
                document_type TEXT,  -- 'url' or 'file'
                document_source TEXT,  -- URL或文件路径
                status TEXT DEFAULT 'draft',  -- draft, generating, completed, failed
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # 测试用例表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS test_cases (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                project_id INTEGER NOT NULL,
                title TEXT,  -- 测试用例标题
                content TEXT NOT NULL,
                raw_content TEXT,  -- AI原始输出
                status TEXT DEFAULT 'draft',  -- draft, approved, archived
                version INTEGER DEFAULT 1,
                generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 生成时间
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE
            )
        ''')
        
        # 代码分析表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS code_analyses (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                project_id INTEGER NOT NULL,
                test_case_id INTEGER,
                frontend_branch TEXT,  -- 前端分支
                backend_branch TEXT,   -- 后端分支
                mr_url TEXT,           -- MR地址
                gitlab_token TEXT,     -- GitLab Token
                projects_info TEXT,    -- JSON格式的项目信息
                analysis_result TEXT,  -- 分析结果文本
                bug_report TEXT,       -- Bug分析报告
                status TEXT DEFAULT 'pending',  -- pending, analyzing, completed, failed
                started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 开始时间
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE,
                FOREIGN KEY (test_case_id) REFERENCES test_cases (id) ON DELETE SET NULL
            )
        ''')
        
        # 创建索引
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_projects_created ON projects(created_at DESC)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_test_cases_project ON test_cases(project_id)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_code_analyses_project ON code_analyses(project_id)')
        
        conn.commit()
        conn.close()
    
    # ==================== 项目管理 ====================
    
    def create_project(self, name: str, description: str = None, 
                      ai_provider: str = 'doubao', ai_model: str = None,
                      ai_key_id: str = None, document_type: str = None,
                      document_source: str = None) -> int:
        """创建项目"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            INSERT INTO projects (name, description, ai_provider, ai_model, 
                                ai_key_id, document_type, document_source)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        ''', (name, description, ai_provider, ai_model, ai_key_id, 
              document_type, document_source))
        
        project_id = cursor.lastrowid
        conn.commit()
        conn.close()
        
        return project_id
    
    def get_project(self, project_id: int) -> Optional[Dict]:
        """获取项目详情"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('SELECT * FROM projects WHERE id = ?', (project_id,))
        row = cursor.fetchone()
        conn.close()
        
        if row:
            return dict(row)
        return None
    
    def list_projects(self, limit: int = 50, offset: int = 0) -> List[Dict]:
        """获取项目列表（按创建时间倒序）"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            SELECT p.*, 
                   COUNT(DISTINCT tc.id) as test_case_count,
                   COUNT(DISTINCT ca.id) as analysis_count
            FROM projects p
            LEFT JOIN test_cases tc ON p.id = tc.project_id
            LEFT JOIN code_analyses ca ON p.id = ca.project_id
            GROUP BY p.id
            ORDER BY p.created_at DESC
            LIMIT ? OFFSET ?
        ''', (limit, offset))
        
        rows = cursor.fetchall()
        conn.close()
        
        return [dict(row) for row in rows]
    
    def update_project(self, project_id: int, **kwargs) -> bool:
        """更新项目"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        # 构建更新语句
        fields = []
        values = []
        for key, value in kwargs.items():
            fields.append(f"{key} = ?")
            values.append(value)
        
        if not fields:
            return False
        
        fields.append("updated_at = CURRENT_TIMESTAMP")
        values.append(project_id)
        
        sql = f"UPDATE projects SET {', '.join(fields)} WHERE id = ?"
        cursor.execute(sql, values)
        
        conn.commit()
        conn.close()
        
        return True
    
    def delete_project(self, project_id: int) -> bool:
        """删除项目"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('DELETE FROM projects WHERE id = ?', (project_id,))
        
        conn.commit()
        conn.close()
        
        return True
    
    # ==================== 测试用例管理 ====================
    
    def create_test_case(self, project_id: int, content: str, 
                        raw_content: str = None, title: str = None) -> int:
        """创建测试用例"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            INSERT INTO test_cases (project_id, title, content, raw_content)
            VALUES (?, ?, ?, ?)
        ''', (project_id, title, content, raw_content))
        
        test_case_id = cursor.lastrowid
        conn.commit()
        conn.close()
        
        return test_case_id
    
    def get_test_case(self, test_case_id: int) -> Optional[Dict]:
        """获取测试用例"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('SELECT * FROM test_cases WHERE id = ?', (test_case_id,))
        row = cursor.fetchone()
        conn.close()
        
        if row:
            return dict(row)
        return None
    
    def get_project_test_cases(self, project_id: int) -> List[Dict]:
        """获取项目的所有测试用例"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            SELECT * FROM test_cases 
            WHERE project_id = ?
            ORDER BY version DESC, created_at DESC
        ''', (project_id,))
        
        rows = cursor.fetchall()
        conn.close()
        
        return [dict(row) for row in rows]
    
    def update_test_case(self, test_case_id: int, content: str) -> bool:
        """更新测试用例"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            UPDATE test_cases 
            SET content = ?, updated_at = CURRENT_TIMESTAMP
            WHERE id = ?
        ''', (content, test_case_id))
        
        conn.commit()
        conn.close()
        
        return True
    
    def delete_test_case(self, test_case_id: int) -> bool:
        """删除测试用例"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('DELETE FROM test_cases WHERE id = ?', (test_case_id,))
        
        conn.commit()
        conn.close()
        
        return True
    
    # ==================== 代码分析管理 ====================
    
    def create_code_analysis(self, project_id: int, test_case_id: int = None,
                           frontend_branch: str = None, backend_branch: str = None,
                           mr_url: str = None, gitlab_token: str = None,
                           projects_info: str = None) -> int:
        """创建代码分析"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            INSERT INTO code_analyses 
            (project_id, test_case_id, frontend_branch, backend_branch, 
             mr_url, gitlab_token, projects_info)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        ''', (project_id, test_case_id, frontend_branch, backend_branch,
              mr_url, gitlab_token, projects_info))
        
        analysis_id = cursor.lastrowid
        conn.commit()
        conn.close()
        
        return analysis_id
    
    def get_code_analysis(self, analysis_id: int) -> Optional[Dict]:
        """获取代码分析"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('SELECT * FROM code_analyses WHERE id = ?', (analysis_id,))
        row = cursor.fetchone()
        conn.close()
        
        if row:
            result = dict(row)
            # 解析JSON字段
            if result.get('analysis_result'):
                try:
                    result['analysis_result'] = json.loads(result['analysis_result'])
                except:
                    pass
            return result
        return None
    
    def get_project_analyses(self, project_id: int) -> List[Dict]:
        """获取项目的所有代码分析"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            SELECT * FROM code_analyses 
            WHERE project_id = ?
            ORDER BY created_at DESC
        ''', (project_id,))
        
        rows = cursor.fetchall()
        conn.close()
        
        results = []
        for row in rows:
            result = dict(row)
            if result.get('analysis_result'):
                try:
                    result['analysis_result'] = json.loads(result['analysis_result'])
                except:
                    pass
            results.append(result)
        
        return results
    
    def update_code_analysis(self, analysis_id: int, analysis_result: Dict = None,
                           bug_report: str = None, status: str = None) -> bool:
        """更新代码分析"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        updates = []
        values = []
        
        if analysis_result is not None:
            updates.append("analysis_result = ?")
            values.append(json.dumps(analysis_result, ensure_ascii=False))
        
        if bug_report is not None:
            updates.append("bug_report = ?")
            values.append(bug_report)
        
        if status is not None:
            updates.append("status = ?")
            values.append(status)
        
        if not updates:
            return False
        
        updates.append("updated_at = CURRENT_TIMESTAMP")
        values.append(analysis_id)
        
        sql = f"UPDATE code_analyses SET {', '.join(updates)} WHERE id = ?"
        cursor.execute(sql, values)
        
        conn.commit()
        conn.close()
        
        return True
    
    def delete_code_analysis(self, analysis_id: int) -> bool:
        """删除代码分析"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('DELETE FROM code_analyses WHERE id = ?', (analysis_id,))
        
        conn.commit()
        conn.close()
        
        return True
    
    def delete_project_analyses(self, project_id: int) -> bool:
        """删除项目的所有代码分析"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('DELETE FROM code_analyses WHERE project_id = ?', (project_id,))
        
        conn.commit()
        conn.close()
        
        return True


# 全局数据库实例
db = Database()

